package org.lanqiao.tjut.Dao;

import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.lanqiao.tjut.Bean.CourseBean;
import org.lanqiao.tjut.Bean.ScoreBean;
import org.lanqiao.tjut.Bean.StudentBean;
import org.lanqiao.tjut.DB.DBDriverFactory;
import org.lanqiao.tjut.MyUtils.MyUtils;

public class StuInfoManagerDao {

	/**
	 * 学生信息保存
	 * 
	 * @param stuB
	 *            学生实体对象
	 * @return 保存结果
	 */
	public int saveStuInfo(StudentBean stuB) {
		int re_i = 0;
		if (stuB.getStuid() == null) {
			// 新增处理
			String strSql = "INSERT INTO EDUSTU(STUNAME ,STUPSW,STUAGE ,STUSEX ,STUTEL,STUADDRESS) VALUES(?,?,?,?,?,?)";
			// 保存数据
			re_i = DBDriverFactory.getDBDriverInstance().update(strSql, new Object[] { stuB.getStuname(),
					stuB.getStupsw(), stuB.getStuage(), stuB.getStusex(), stuB.getStutel(), stuB.getStuaddress() });
		} else {
			// 修改处理
			String strSql = "UPDATE EDUSTU SET STUNAME = ?,STUPSW = ?,STUAGE = ?,STUSEX = ?,STUTEL = ?,STUADDRESS=? WHERE STUID = ?";
			// 保存数据
			re_i = DBDriverFactory.getDBDriverInstance().update(strSql,
					new Object[] { stuB.getStuname(), stuB.getStupsw(), stuB.getStuage(), stuB.getStusex(),
							stuB.getStutel(), stuB.getStuaddress(), stuB.getStuid() });
		}

		return re_i;
	}

	/**
	 * 获取所有学生信息
	 * 
	 * @return 学生信息结果集
	 */
	public List<StudentBean> getAllStuInfo() {
		// 查询数据库的sql语句
		String strSql = "SELECT * FROM EDUSTU ORDER BY STUID DESC";
		List<StudentBean> lstStu = DBDriverFactory.getDBDriverInstance().query(strSql,
				new BeanListHandler<StudentBean>(StudentBean.class));
		return lstStu;
	}

	/**
	 * 获取指定学生信息
	 * 
	 * @param strUid
	 *            指定学生ID
	 * @return 学生信息
	 */
	public StudentBean getStuInfoById(String strUid) {
		// 查询数据
		// 查询数据库的sql语句
		String strSql = "SELECT * FROM EDUSTU WHERE STUID=?";
		StudentBean stuB = DBDriverFactory.getDBDriverInstance().query(strSql,
				new BeanHandler<StudentBean>(StudentBean.class), new Object[] { strUid });
		return stuB;
	}

	/**
	 * 根据查询条件查询学生信息
	 * 
	 * @param stuB
	 *            学生实体对象
	 * @return 查询结果集
	 */
	public List<StudentBean> doQueryByParas(StudentBean stuB) {
		// 根据条件进行数据查询，没有输入查询条件，则进行全部查询
		StringBuilder sbSql = new StringBuilder("SELECT * FROM EDUSTU WHERE 1=1 ");
		// 根据查询条件动态拼写查询语句和参数
		List<Object> lstParas = new ArrayList<Object>();
		if (stuB.getStuname() != null && !stuB.getStuname().trim().equals("")) {
			// 姓名查询
			sbSql.append(" AND STUNAME LIKE ? ");
			// 添加参数
			lstParas.add("%%" + stuB.getStuname() + "%%");
		}
		if (stuB.getStusex() != null && !stuB.getStusex().trim().equals("")) {
			// 姓名查询
			sbSql.append(" AND STUSEX = ? ");
			// 添加参数
			lstParas.add(stuB.getStusex());
		}
		if (stuB.getStuage() > 0) {
			// 姓名查询
			sbSql.append(" AND STUAGE >= ? ");
			// 添加参数
			lstParas.add(stuB.getStuage());
		}
		if (stuB.getStuage2() > 0) {
			// 姓名查询
			sbSql.append(" AND STUAGE <= ? ");
			// 添加参数
			lstParas.add(stuB.getStuage2());
		}

		// 进行排序
		sbSql.append("  ORDER BY STUID ");
		// 进行查询操作
		List<StudentBean> lstStu = DBDriverFactory.getDBDriverInstance().query(sbSql.toString(),
				new BeanListHandler<StudentBean>(StudentBean.class), lstParas.toArray());
		return lstStu;
	}

	/**
	 * 删除指定学生信息
	 * 
	 * @param userId
	 *            指定学生ID
	 * @return 删除结果
	 */
	public int deleteStuById(int stuId) {
		int re_i = 0;
		// 要执行的sql语句
		String strSql = "DELETE FROM EDUSTU WHERE STUID=?";
		// 删除数据
		re_i = DBDriverFactory.getDBDriverInstance().update(strSql, new Object[] { stuId });
		return re_i;
	}

	/**
	 * 根据教师id获取该教师所教授课程的信息列表
	 * 
	 * @param teaId
	 *            教师id
	 * @return 教授课程的信息列表
	 */
	public List<CourseBean> getCourseInfoByTeaId(int teaId) {

		// 查询数据库的sql语句
		String strSql = "SELECT * FROM EDUSCORE WHERE COUID IN (SELECT COUID FROM EDUSCORE WHERE TEAID=?)";
		List<CourseBean> lstCourse = DBDriverFactory.getDBDriverInstance().query(strSql,
				new BeanListHandler<CourseBean>(CourseBean.class), new Object[] { teaId });
		return lstCourse;
	}

	/**
	 * 获取老师教授课程的学生信息
	 * 
	 * @param teaId
	 *            教师id
	 * @param courseId
	 *            课程id
	 * @return 学生信息
	 */
	public List<ScoreBean> getStuInfoBy4Grade(int teaId, int courseId) {
		// 查询数据库的sql语句
		String strSql = "SELECT * FROM EDUSCORE WHERE 1=1 AND TEAID=? AND COUID=?";
		List<ScoreBean> lstStuInfo4Grade = DBDriverFactory.getDBDriverInstance().query(strSql,
				new BeanListHandler<ScoreBean>(ScoreBean.class), new Object[] { teaId, courseId });
		return lstStuInfo4Grade;
	}

	public int updateStuScore(String strParasScores) {
		String strSql = "UPDATE EDUSCORE SET SCORE = ? WHERE SCOID=?";
		int[] i = DBDriverFactory.getDBDriverInstance().batch(strSql, paseStrParas2Objects(strParasScores));
		return 1;
	}

	/**
	 * 将字符串转换为批处理需要的二维数组参数的格式
	 * 
	 * @param strParasScores
	 *            参数字符串
	 * @return 二维数组参数
	 */
	private Object[][] paseStrParas2Objects(String strParasScores) {
		Object[][] arrParas = null;
		if (strParasScores != null && !strParasScores.trim().equals("")) {
			// 解析字符串
			String[] strArr = strParasScores.split(",");
			arrParas = new Object[strArr.length][2];
			for (int i = 0; i < strArr.length; i++) {
				String[] strSubArr = strArr[i].split(":");
				if (strSubArr.length == 2) {
					// 参数数组
					arrParas[i] = new Object[] { strSubArr[1], strSubArr[0] };
				} /*
					 * else{ arrParas[i] = new Object[] { null, strSubArr[0] };
					 * }
					 */
			}
		}
		MyUtils.logger_console.debug(arrParas);
		return arrParas;
	}

	/**
	 * 根据课程id获取学生信息
	 * 
	 * @param couid
	 *            课程id
	 * @return 查询结果集
	 */
	public List<ScoreBean> getStuInfoByCouid(String couid) {
		// 查询数据库的sql语句
		String strSql = "SELECT STUNAME,STUID,COUNAME,SCORE FROM EDUSCORE WHERE 1=1 AND  COUID=?";
		List<ScoreBean> lstSco = DBDriverFactory.getDBDriverInstance().query(strSql,
				new BeanListHandler<ScoreBean>(ScoreBean.class), new Object[] { couid });
		return lstSco;
	}

	/**
	 * 新增学生成绩
	 * 
	 * @param strScore
	 *            学生成绩
	 * @return 新增结果
	 */
	public int updateScore(String strScore,String strstuid) {
		String strSql = "UPDATE EDUSCORE SET SCORE = ? WHERE SCOID=?";
		int i = DBDriverFactory.getDBDriverInstance().update(strSql, new Object[]{strScore,strstuid});
		return i;
	}
}
